package thirdPartyUtils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import lombok.Data;
import org.junit.Test;

import java.util.*;

public class EasyExcelTest {

    // 1、简单导出
    @Test
    public void simpleExport(){

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        String fileName = "simpleExportOut.xlsx";
        EasyExcel.write(fileName, User.class).sheet("自定义sheet名").doWrite(data());

        // 可选：控制导出的表头
//        List<List<String>> headList = new ArrayList<>();
//        headList.add(Lists.newArrayList("姓名"));
//        //headList.add(Lists.newArrayList("年龄"));
//        //headList.add(Lists.newArrayList("操作时间"));

        //可选：指定写入的列或者排除特定的列
//        Set<String> includeColumnFiledNames = new HashSet<String>();
//        includeColumnFiledNames.add("birthday");
//        EasyExcel.write(fileName, User.class).head(headList).includeColumnFiledNames(includeColumnFiledNames).sheet("自定义sheet名").doWrite(userList);
    }


    @Test  //分批写单个sheet
    public void writeBatch() {
        ExcelWriter excel = EasyExcel.write("exportOutMultiBatch.xlsx").build();
        WriteSheet sheet = EasyExcel.writerSheet("sheetExample").head(User.class).build();
        excel.write(data(), sheet);
        excel.write(data(), sheet);

        WriteSheet sheet2 = EasyExcel.writerSheet("sheetExample2").head(User2.class).build();
        excel.write(data2(), sheet2);

        excel.finish();
    }

    //填充模板
    //2、普通填充模板
    @Test
    public void simpleFill(){
        String fileName = "simpleFillOut.xlsx";
        String templateFileName = "simpleFill.xlsx";

        User user = new User();
        user.setUsername("用户1");
        user.setPassword(String.valueOf(Math.random()));
        user.setBirthday(new Date());
        user.setMoney(3.14);

        EasyExcel.write(fileName)
                 .withTemplate(this.getClass().getResourceAsStream("/excelTemplate/simpleFill.xlsx"))
                 .sheet("Sheet1").doFill(user);
        //注意点 1、无User.class 2.sheetName指代模板的sheet名 3.和字段上的@ExcelProperty注解无关
    }

    @Test
    public void listFill(){
        // 3、列表填充模板
        String templateFileName = "listFill.xlsx";
        EasyExcel.write("listFillOut.xlsx").withTemplate(templateFileName).sheet("Sheet1").doFill(data());
    }

    @Test
    public void complexFill(){
        // 4、混合填充模板
        String fileName = "complexFillOut.xlsx";
        String templateFileName = "complexFill.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();


        //如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true
        //但是这个就会把所有数据放到内存 会很耗内存
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(data(), fillConfig , EasyExcel.writerSheet().build());


        Map<String, Object> map = new HashMap<String, Object>();
        map.put("date", "2019年10月9日13:28:28");
        map.put("total", 1000);

        excelWriter.fill(map, EasyExcel.writerSheet().build());
        excelWriter.finish();
    }

    @Test
    public void multiSheetFill(){
        String fileName = "multiSheetFillOut.xlsx";
        String templateFileName = "multiSheetFill.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
        WriteSheet sheet1 = EasyExcel.writerSheet("Sheet1").build();
        WriteSheet sheet2 = EasyExcel.writerSheet("Sheet2").build();

        excelWriter.fill(data(), sheet1);
        excelWriter.fill(data(), sheet2);
        excelWriter.finish();
    }

    private List<User> data(){
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUsername("用户" + i);
            user.setPassword(String.valueOf(Math.random()));
            user.setBirthday(new Date());
            user.setMoney(3.14);
            userList.add(user);
        }
        return userList;
    }

    private List<User2> data2(){
        List<User2> userList = new ArrayList<User2>();
        for (int i = 0; i < 10; i++) {
            User2 user = new User2();
            user.setUsername("用户" + i);
            userList.add(user);
        }
        return userList;
    }
}

@Data
class User {
    @ExcelIgnore
    private Long id;

    @ExcelProperty("用户名")
    private String username;
    @ExcelIgnore
    private String password;
    @ExcelProperty("生日")
    private Date birthday;
    @ExcelProperty("余额")
    private Double money;
}

@Data
class User2 {
    @ExcelIgnore
    private Long id;

    @ExcelProperty("用户名")
    private String username;
}